package question;

import utils.DBConnection;
import utils.TokenUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: Shawn
 * Date: 2009-6-16
 * Time: 21:33:50
 */
public class Classify {

  public static int addClassify(Connection con, ClassifyInfo info) throws SQLException {
    String sql = "INSERT INTO classify VALUES (NULL, ?, ?, ?)";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1, info.getCname());
    ps.setInt(2, info.getCtype());
    ps.setInt(3, info.getParentID());
    ps.executeUpdate();
    return DBConnection.getLastID(con);
  }

  public static void delClassify(Connection con, int[] ids) throws SQLException {
    if (ids == null || ids.length == 0) return;
    String sql = "DELETE FROM classify WHERE cid IN (" + TokenUtils.getTokenString(ids, ",") + ")";
    con.createStatement().executeUpdate(sql);
  }

  public static ClassifyInfo getClassify(Connection con, int cid) throws SQLException {
    String sql = "SELECT * FROM classify WHERE cid=? LIMIT 1";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, cid);
    ResultSet rs = ps.executeQuery();
    return rs.next() ? rs2Info(rs) : new ClassifyInfo();
  }

  public static int getKmID(Connection con, String km) throws SQLException {
    String sql = "SELECT * FROM classify WHERE cname=? LIMIT 1";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1, km);
    ResultSet rs = ps.executeQuery();
    return !rs.next() ? -1 : rs2Info(rs).getCid();
  }

  public static List<ClassifyInfo> getClassifyList(Connection con) throws SQLException {
    String sql = "SELECT * FROM classify";
    ResultSet rs = con.createStatement().executeQuery(sql);

    List<ClassifyInfo> list = new ArrayList<ClassifyInfo>();
    while (rs.next()) list.add(rs2Info(rs));
    return list;
  }

  public static List<ClassifyInfo> getClassifyList(Connection con, int type) throws SQLException {
    String sql = "SELECT * FROM classify WHERE ctype=" + type;
    ResultSet rs = con.createStatement().executeQuery(sql);

    List<ClassifyInfo> list = new ArrayList<ClassifyInfo>();
    while (rs.next()) list.add(rs2Info(rs));
    return list;
  }

  public static List<ClassifyInfo> getChildren(Connection con, int parentID) throws SQLException {
    String sql = "SELECT * FROM classify WHERE parentID=" + parentID;
    ResultSet rs = con.createStatement().executeQuery(sql);

    List<ClassifyInfo> list = new ArrayList<ClassifyInfo>();
    while (rs.next()) list.add(rs2Info(rs));
    return list;
  }

  public static List<ClassifyInfo> getYdList(Connection con, int kmID) throws SQLException {
    List<ClassifyInfo> dtList = getChildren(con, kmID);
    List<ClassifyInfo> ydList = new ArrayList<ClassifyInfo>();
    for (ClassifyInfo info : dtList) ydList.addAll(getChildren(con, info.getCid()));
    return ydList;
  }

  public static List<ClassifyInfo> getKmList(Connection con, int kmID) throws SQLException {
    List<ClassifyInfo> result = new ArrayList<ClassifyInfo>();
    result.add(getClassify(con, kmID));
    List<ClassifyInfo> dtList = getChildren(con, kmID);
    for (ClassifyInfo info : dtList) result.add(info);
    List<ClassifyInfo> ydList = getYdList(con, kmID);
    for (ClassifyInfo info : ydList) result.add(info);
    return result;
  }

  private static ClassifyInfo rs2Info(ResultSet rs) throws SQLException {
    ClassifyInfo info = new ClassifyInfo();
    info.setCid(rs.getInt("cid"));
    info.setCname(rs.getString("cname"));
    info.setCtype(rs.getInt("ctype"));
    info.setParentID(rs.getInt("parentID"));
    return info;
  }
}
